import pymysql
import json

conn = pymysql.Connect(  # 连接数据库
    host='192.168.175.176',
    port=3306,
    user='root',
    passwd='Firmament.Mysql!99',
    db='firmament_cloud',
    charset='utf8'
)

cursor = conn.cursor()

# 查询所有数据
cursor.execute('SELECT dict_id, dict_pid, dict_name FROM sys_dict')
rows = cursor.fetchall()

# 构建节点字典
node_dict = {}
for row in rows:
    dict_id, dict_pid, dict_name = row
    node = {
        'dict_id': dict_id,
        'dict_name': dict_name,
        'children': []
    }
    node_dict[dict_id] = node

# 构建树形结构
root_nodes = []
for row in rows:
    dict_id, dict_pid, _ = row
    if dict_pid is None:
        root_nodes.append(node_dict[dict_id])
    else:
        parent = node_dict.get(dict_pid)
        if parent:
            parent['children'].append(node_dict[dict_id])

# 转换为 JSON 并输出
tree_json = json.dumps(root_nodes, indent=4, ensure_ascii=False)
print(tree_json)

# 关闭数据库连接
conn.close()